package com.aischool.service;

import java.util.ArrayList;
import java.util.List;

import com.aischool.common.model.AiCourse;
import com.aischool.params.AiCourseParams;
import com.aischool.utils.RegexUtil;
import com.aischool.utils.StateEnum;
import com.aischool.utils.StringUtil;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

public class AiCourseService extends BaseService {
	/**
	 * 获取分页数据
	 * @param params
	 * @return
	 */
	public Page<Record> getPage(AiCourseParams params){
		String sql_select = "SELECT a.course_id, a.course_name, a.front_cover_url, b.type_name, c.grade_name, a.course_tab, a.power_id, a.add_time, a.course_state, a.org_id,a.classes_num ";
		String sql_from   =	"FROM ai_course a " + 
				"INNER JOIN ai_course_type b ON b.type_id = a.course_typeid " + 
				"INNER JOIN ai_grade c ON c.grade_id = a.grade_id ";
		String sql_where  =	"WHERE a.course_state >= 0 ";
		String sql_order = "ORDER BY a.course_id DESC ";
		
		List<Object> listObj = new ArrayList<Object>();
		if (params.getState() != StateEnum.ALL.getState()) {
			sql_where =	"WHERE a.course_state = ?";
			listObj.add(params.getState());
		}
		
		int idx = 0;
		if (params.getPoid()>0) {
			sql_where += " AND a.prelect_object_id = ? ";
			listObj.add(params.getPoid());
		}
		
		if (params.getOrgId() > 0) {
			sql_from += "INNER JOIN (SELECT regexp_split_to_table(COALESCE(?,''),E',') AS ids) AS tt " + 
					"ON CAST(tt.ids AS INTEGER) = a.org_id ";
			String oids = "0," + params.getOrgId();
			listObj.add(idx, oids);
			idx++;
			
		}else {
			sql_where += " AND a.org_id = ? ";
			listObj.add(params.getOrgId());
		}
		
		if (params.getArticle_Catid()>0) {
			sql_select += ", cc.cat_name ";
			sql_from += "INNER JOIN (SELECT cat_id,cat_name FROM ai_article_cat WHERE cat_id = ? OR parent_id = ?) AS cc " + 
					"		ON cc.cat_id = a.article_catid ";
			listObj.add(idx, params.getArticle_Catid());
			idx++;
			listObj.add(idx, params.getArticle_Catid());
			idx++;
		}
				
		if (StrKit.notBlank(params.getKeywords())) {
			sql_where += " AND a.course_name LIKE ? ";
			listObj.add("%" + params.getKeywords() + "%");
		}
		return Db.paginate(params.getPageNo(), params.getPageSize(), sql_select, sql_from + sql_where + sql_order, listObj.toArray());
	}
	
	public AiCourse getInfo(int course_id) {
		Record rd = getOne(course_id);
		if (rd!=null) {
			return new AiCourse().put(rd);
		}
		return null;
	}
	
	public Record getOne(int course_id) {
		return Db.findFirst("SELECT a.*, b.type_name, c.grade_name, d.subject_name, e.prelect_object_name "+ 
				"FROM ai_course a \r\n" + 
				"INNER JOIN ai_course_type b ON b.type_id = a.course_typeid \r\n" + 
				"INNER JOIN ai_grade c ON c.grade_id = a.grade_id \r\n" + 
				"INNER JOIN ai_course_subject d ON d.subject_id = a.subject_id 	\r\n" + 
				"INNER JOIN ai_prelect_object e ON e.prelect_object_id = a.prelect_object_id " + 
				"WHERE a.course_id = ?", course_id);
	}
	
	public boolean del(int course_id) {
		int ir = Db.delete("DELETE FROM ai_course WHERE course_id = ?", course_id);
		return ir > 0;
	}
	
	public boolean delForOrg(int org_id, int course_id) {
		int ir = Db.delete("DELETE FROM ai_course WHERE org_id = ? AND course_id = ?", org_id, course_id);
		return ir > 0;
	}
	
	public boolean delMore(String ids) {
		if (StrKit.isBlank(ids)) {
			return false;
		}
		if (RegexUtil.isSqlIds(ids)) {
			int ir = Db.delete("DELETE FROM ai_course a " + 
					"USING (SELECT regexp_split_to_table(COALESCE(?,''),E',') AS ids) AS tt " +
					"WHERE a.course_id = CAST(tt.ids AS INTEGER)", 
					StringUtil.trim(ids, ','));
			return ir > 0;
		}
		
		return false;
	}
	
	public boolean delMoreForOrg(int org_id, String ids) {
		if (StrKit.isBlank(ids)) {
			return false;
		}
		if (RegexUtil.isSqlIds(ids)) {
			int ir = Db.delete("DELETE FROM ai_course a " + 
					"USING (SELECT regexp_split_to_table(COALESCE(?,''),E',') AS ids) AS tt " +
					"WHERE a.org_id = ? AND a.course_id = CAST(tt.ids AS INTEGER)", 
					StringUtil.trim(ids, ','), org_id);
			return ir > 0;
		}
		
		return false;
	}
}
